package com.db;

import com.to.CategoryTo;
import com.to.ResultTo;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.scene.chart.PieChart;
import javafx.scene.chart.XYChart;

public class DashBoardDao implements Serializable{

        private static final long serialVersionUID = 1L;
        Connection conn = null;
        DecimalFormat df=new DecimalFormat("0.00");
        /**
         * This method get all the category from the master_category table
         * and return them as a list.
         * This list is used for generation the Product category combo box
         *
         */
        
        public List<CategoryTo> getAllProductCat(){
                List<CategoryTo> list = new ArrayList<CategoryTo>();
                CategoryTo to = null;
                try{
                        conn = CreateConnection.getCon();
                        PreparedStatement st = (PreparedStatement) conn.prepareStatement("SELECT * FROM  master_category ");
                                
                        ResultSet res = st.executeQuery();
                              
                        while (res.next()) {
                                to = new CategoryTo();
                                to.setId(res.getInt("cat_id"));
                                to.setDesc(res.getString("cat_desc"));
                                list.add(to);
                        }
                        res.close();
                        st.close();
                        conn.close();
                }catch(Exception e){
                        e.printStackTrace();
                }
                
                return list;
        }
        
        /**
         * This method get the total sales amount (net price) and the percentage of total sales
         *  for every products  category   in the form of a Observable list,
         *  which is used to prepare the table for the Category details in dash board1
         */

        public ObservableList<ResultTo> getCatResult(){
            ObservableList<ResultTo> data = FXCollections.observableArrayList();
                
            try{

                double total = 0;
                conn = CreateConnection.getCon();
                String query1 = " select sum(so.net_price)from sales_order so,product_detail pd ,master_category mc " +
                                        " where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id   ";

                PreparedStatement st1 = (PreparedStatement) conn.prepareStatement(query1);

                ResultSet res1 = st1.executeQuery();
                while (res1.next()) {
                        total  = res1.getDouble(1);
                }
                res1.close();
                st1.close();
                DecimalFormat df=new DecimalFormat("0.00");     
                String query = "  select mc.cat_desc,sum(so.net_price),st.target_cost "
                        + " from sales_order so,product_detail pd ,master_category mc,sales_target st " +
                                " where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id   " +
                                "   and mc.cat_id = st.cat_id  group by mc.cat_id";
                PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);

                ResultSet res = st.executeQuery();

                while (res.next()) {
                    String formate = df.format((res.getDouble(2)*100)/total);
                    double percentValue = (Double)df.parse(formate) ;
                    double netSale = (Double)df.parse(df.format((res.getDouble(2))/1000));
                    double mrkt = getMrktCost(res.getString(1));
                    double trgt_cost = res.getDouble(3)/1000;
                    data.add(new ResultTo(res.getString(1), netSale, percentValue, trgt_cost, mrkt, mrkt, netSale));
                    
                    
                }
                res.close();
                st.close();
                conn.close();
            }catch(SQLException e){
                    e.printStackTrace();
            }catch(ParseException e){

            }

            return data;
        }
        
        
        /**
         * This method get the total sales amount (net price) and the percentage of total sales
         *  for every products for a given product category   in the form of a Observable list,
         *  which is used to prepare the product pie chart
         */
    
        @SuppressWarnings({ "rawtypes", "unchecked" })
  public ObservableList getDataCatwise(String cat) {
                
                ObservableList data = FXCollections.observableArrayList();
                try {
                        double total = 0;
                        conn = CreateConnection.getCon();
                        String query1 = "  select sum(so.net_price)from sales_order so,product_detail pd ,master_category mc " +
                                        " where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id and mc.cat_desc = ?  ";
                                        
                        PreparedStatement st1 = (PreparedStatement) conn.prepareStatement(query1);
                        st1.setString(1, cat);
                        ResultSet res1 = st1.executeQuery();
                        while (res1.next()) {
                                total  = res1.getDouble(1);
                        }
                        res1.close();
                        st1.close();
                        //conn = CreateConnection.getCon();
                        String query = "  select pd.product_desc,sum(so.net_price)from sales_order so,product_detail pd ,master_category mc " +
                                        " where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id and mc.cat_desc = ?  " +
                                        "  group by so.product_id";
                        PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
                        st.setString(1, cat);
                        ResultSet res = st.executeQuery();
                           System.out.println("total :" + total);
                           DecimalFormat df=new DecimalFormat("0.00");

                        while (res.next()) {
                            double temp = Math.round(res.getDouble(2)*100)/total;
                            
                            String formate = df.format(temp);
                           // Number  finalValue =df.parse(formate) ;
                            // System.out.println("total by :" + res.getDouble(2) +" and by 100 " +(res.getDouble(2)*100)/total + "final val : "+ finalValue);
                                data.add(new PieChart.Data(res.getString(1),new Double(formate)));

                        }
                        res.close();
                        st.close();
                        conn.close();
                        
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                
                return data;
                
                
        }
        
        
        /**
         * This method get the total sales amount (net price) and the percentage of total sales
         *  for every products for a given product category   in the form of a Observable list,
         *  which is used to prepare the product table
         * @throws ParseException
         */
        public ObservableList<ResultTo> getProdResult(String cat) {
            ObservableList<ResultTo> data = FXCollections.observableArrayList();
                
            try{
                conn = CreateConnection.getCon();
                double total = 0;
                conn = CreateConnection.getCon();
                String query1 = "  select sum(so.net_price)from sales_order so,product_detail pd ,master_category mc " +
                                " where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id and mc.cat_desc = ?  ";
                                        
                PreparedStatement st1 = (PreparedStatement) conn.prepareStatement(query1);
                st1.setString(1, cat);
                ResultSet res1 = st1.executeQuery();
                while (res1.next()) {
                        total  = res1.getDouble(1);
                }
                res1.close();
                st1.close();
                DecimalFormat df=new DecimalFormat("0.00");
                        
                String query = "  select pd.product_desc,sum(so.net_price)from sales_order so,product_detail pd ,master_category mc " +
                                " where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id and mc.cat_desc = ?  " +
                                "  group by so.product_id";
                PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
                st.setString(1, cat);
                ResultSet res = st.executeQuery();
                                  
                while (res.next()) {
                    double temp = Math.round(res.getDouble(2)*100)/total;
                    String formate = df.format(temp);
                    double netSale = (Double)df.parse(df.format((res.getDouble(2))/1000));
                    data.add(new ResultTo(res.getString(1),netSale,new Double(formate)));
                }
                res.close();
                st.close();
                conn.close();
                }catch(Exception e){
                        e.printStackTrace();
                }
                        
            return data;
        }
        
        
        /**
         * This method get the total sales amount (net price)
         *  for every  product category   in the form of a Chart series,
         *  which is used to prepare the product category bar chart
         */
        @SuppressWarnings({ "unchecked", "rawtypes" })
  public XYChart.Series<String,Double> getBarChart(){
                XYChart.Series< String,Double> aSeries = new XYChart.Series<String,Double>();
                try{
                        conn = CreateConnection.getCon();
                        String query = "  select mc.cat_desc,sum(so.net_price)from sales_order so,product_detail pd ,master_category mc " +
                                        " where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id   " +
                                        "  group by mc.cat_id";
                        PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
                        
                        ResultSet res = st.executeQuery();
                        DecimalFormat df=new DecimalFormat("0.00");
                        while (res.next()) {
                        	 double temp = Math.round(res.getDouble(2))/1000;
                        	 String formate = df.format(temp);
                                aSeries.getData().add(new XYChart.Data(res.getString(1),new Double(formate)));
                        }
                        res.close();
                        st.close();
                        conn.close();
                }catch(SQLException e){
                        e.printStackTrace();
                }
                return aSeries;
                
        }
        
        /**
         * This method get the total sales amount of all products
         */
        
        public double getTotalSales(){
                double total = 0;
                try{
                        conn = CreateConnection.getCon();
                        String query = "select sum(so.net_price) from sales_order so ";
                        PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
                        
                        ResultSet res = st.executeQuery();
                        while (res.next()) {
                                total = res.getDouble(1);
                        }
                        res.close();
                        st.close();
                        conn.close();
                
                }catch(SQLException e){
                        e.printStackTrace();
                }
        return total;
        }

        
        /**
         * Getting the total marketing expenditure of the products
         * in a particular month
         * and for a particular category
         * in form of a pie chart
         */
        
        @SuppressWarnings({ "rawtypes", "unchecked" })
  public ObservableList getMarketingDataCatwise(String catName,String quater) {
            
            ObservableList data = FXCollections.observableArrayList();
            String start_date = null;
            String end_date = null;
            String query = null;
            PreparedStatement st = null;
            try {
                double total = 0;
                conn = CreateConnection.getCon();
                
                if("Q3 - 2012".equalsIgnoreCase(quater)){
                	start_date = "01/07/2012";
                	end_date = "30/9/2012";
                }else if("Q4 - 2012".equalsIgnoreCase(quater)){
                	start_date = "01/10/2012";
                	end_date = "31/12/2012";
                }
                
                
              
                //getting the total marketing cost for of a category
                String query1 = "select sum(pd.discount * pd.planed_volume) as Marketing_cost , prd.product_desc "
                        + " from promotion_details pd ,product_detail prd, master_category cat "
                        + " where pd.product_id = prd.product_id  and prd.product_cat_id = cat.cat_id "
                        + " and cat.cat_desc = ?  and  pd.start_date >= STR_TO_DATE(?, '%d/%m/%Y %h:%i:%s') and " +
                        "		 pd.start_date  < STR_TO_DATE(?,'%d/%m/%Y %h:%i:%s')  "
                        + " group by  cat.cat_id ; ";
                                        
                PreparedStatement st1 = (PreparedStatement) conn.prepareStatement(query1);
                st1.setString(1, catName);
                st1.setString(2, start_date);
                st1.setString(3, end_date);
                ResultSet res = st1.executeQuery();
                while (res.next()) {
                        total  = res.getDouble(1);
                }
                res.close();
                st1.close();
                
                //getting marketing cost for all products for a category
                
                DecimalFormat df=new DecimalFormat("0.00");
                String query2 = "select sum(pd.discount * pd.planed_volume) as Marketing_cost , prd.product_desc "
                        + " from promotion_details pd ,product_detail prd, master_category cat "
                        + " where pd.product_id = prd.product_id  and prd.product_cat_id = cat.cat_id "
                        + " and cat.cat_desc = ?  and  pd.start_date >= STR_TO_DATE(?, '%d/%m/%Y %h:%i:%s') and " +
                        "		 pd.start_date  < STR_TO_DATE(?,'%d/%m/%Y %h:%i:%s')  "
                        + " group by pd.product_id ;";
                st1 = (PreparedStatement) conn.prepareStatement(query2);
                st1.setString(1, catName);
                st1.setString(2, start_date);
                st1.setString(3, end_date);
                res = st1.executeQuery();

                while (res.next()) {
                    double temp = Math.round(res.getDouble(1)*100)/total;
                    String formate = df.format(temp);
                    data.add(new PieChart.Data(res.getString(2),new Double(formate)));
                }
                res.close();
                st1.close();
                conn.close();
                        
            } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
            }
                return data;
                
                
        }
        
        
         /**
         * Getting the total marketing expenditure of the products
         * in a particular month
         * and for a particular category
         * in form of a Table view
         */
        
        public ObservableList<ResultTo> getMarketingCatWise(String catName, String  quater){
            ObservableList<ResultTo> data = FXCollections.observableArrayList();
            String start_date = null;
            String end_date = null;
            try{
                conn = CreateConnection.getCon();
                double total = 0;

                if("Q3 - 2012".equalsIgnoreCase(quater)){
                	start_date = "01/07/2012";
                	end_date = "30/9/2012";
                }else if("Q4 - 2012".equalsIgnoreCase(quater)){
                	start_date = "01/10/2012";
                	end_date = "31/12/2012";
                }
                //getting the total marketing cost for of a category
                String query1 = "select sum(pd.discount * pd.planed_volume) as Marketing_cost , prd.product_desc "
                        + " from promotion_details pd ,product_detail prd, master_category cat "
                        + " where pd.product_id = prd.product_id  and prd.product_cat_id = cat.cat_id "
                        + " and cat.cat_desc = ?  and  pd.start_date >= STR_TO_DATE(?, '%d/%m/%Y %h:%i:%s') and " +
                        "		 pd.start_date  < STR_TO_DATE(?,'%d/%m/%Y %h:%i:%s')  "
                        + " group by  cat.cat_id ; ";
                                        
                PreparedStatement st1 = (PreparedStatement) conn.prepareStatement(query1);
                st1.setString(1, catName);
                st1.setString(2, start_date);
                st1.setString(3, end_date);
                ResultSet res = st1.executeQuery();
                while (res.next()) {
                        total  = res.getDouble(1);
                }
                res.close();
                st1.close();
                
                //getting marketing cost for all products for a category
                
                DecimalFormat df=new DecimalFormat("0.00");
                String query2 = "select sum(pd.discount * pd.planed_volume) as Marketing_cost , prd.product_desc "
                        + " from promotion_details pd ,product_detail prd, master_category cat "
                        + " where pd.product_id = prd.product_id  and prd.product_cat_id = cat.cat_id "
                        + " and cat.cat_desc = ?  and  pd.start_date >= STR_TO_DATE(?, '%d/%m/%Y %h:%i:%s') and " +
                        "		 pd.start_date  < STR_TO_DATE(?,'%d/%m/%Y %h:%i:%s')  "
                        + " group by pd.product_id ;";
                st1 = (PreparedStatement) conn.prepareStatement(query2);
                st1.setString(1, catName);
                st1.setString(2, start_date);
                st1.setString(3, end_date);
                res = st1.executeQuery();
                while (res.next()) {
                    double temp = Math.round(res.getDouble(1)*100)/total;
                    String formate = df.format(temp);
                    double mrkt_cost = res.getDouble(1)/1000;
                    data.add(new ResultTo(res.getString(2),mrkt_cost,new Double(formate)));
                }
                res.close();
                st1.close();
                conn.close();
                
            }catch(Exception e){
                    e.printStackTrace();
            }

            return data;
        }
        
        /**
         * Get all the distinct month and year from the promotion_details to generate
         * Period drop down in the dashboard2
         */
        
        public List<String> getPeriod(){
            List<String> list = new ArrayList<String>();
           
            try{
                conn = CreateConnection.getCon();
                list.add("Q3 - 2012");
                list.add("Q4 - 2012");
               
            }catch(Exception e){
                    e.printStackTrace();
            }
            
            return list;
        }
        
        
        /**
         * This method returns the month name from the month number
         */
        private String getMonthName(int month){
            String monthString = null;
            switch (month) {
                case 1:  monthString = "JAN";
                         break;
                case 2:  monthString = "FEB";
                         break;
                case 3:  monthString = "MAR";
                         break;
                case 4:  monthString = "APR";
                         break;
                case 5:  monthString = "MAY";
                         break;
                case 6:  monthString = "JUN";
                         break;
                case 7:  monthString = "JUL";
                         break;
                case 8:  monthString = "AUG";
                         break;
                case 9:  monthString = "SEP";
                         break;
                case 10: monthString = "OCT";
                         break;
                case 11: monthString = "NOV";
                         break;
                case 12: monthString = "DEC";
                         break;
               
            }
            
            return monthString;
        }

/**
 * This method to return the marketing expenditure of a category in a series format.
 * Its used to generate the area chart for marketing cost in the marketing dash board.
 */
    @SuppressWarnings({ "unchecked", "rawtypes" })
  public XYChart.Series<String,Double> getAreaMarketChart(final String catName, final String quater){
                XYChart.Series< String,Double> aSeries = new XYChart.Series<String,Double>();
                String start_date = null;
                String end_date = null;
                String query = null;
                PreparedStatement st = null;
                try{
                        conn = CreateConnection.getCon();
                        
                        if("Q3 - 2012".equalsIgnoreCase(quater)){
                        	start_date = "01/07/2012";
                        	end_date = "30/9/2012";
                        }else if("Q4 - 2012".equalsIgnoreCase(quater)){
                        	start_date = "01/10/2012";
                        	end_date = "31/12/2012";
                        }
                        
                        
                        if("All".equalsIgnoreCase(catName)){
                        	query = " select DATE_FORMAT(prd.start_date,GET_FORMAT(DATE,'EUR')), sum(prd.planed_volume * prd.discount) " +
                        			"     from promotion_details prd,  product_detail pd, master_category mc " +
                        			"      where  prd.start_date >= STR_TO_DATE(?, '%d/%m/%Y %h:%i:%s') and  " +
                        			"		 prd.start_date  < STR_TO_DATE(?,'%d/%m/%Y %h:%i:%s')  " +
                        			"        and prd.product_id = pd.product_id and pd.product_cat_id = mc.cat_id " +
                        			"   group by  prd.start_date ";
                        	
                        	st = (PreparedStatement) conn.prepareStatement(query);
                        	st.setString(1, start_date);
                        	st.setString(2, end_date);
                        }else{
                        	query = " select DATE_FORMAT(prd.start_date,GET_FORMAT(DATE,'EUR')), sum(prd.planed_volume * prd.discount) " +
                        			"     from promotion_details prd,  product_detail pd, master_category mc " +
                        			"      where  prd.start_date >= STR_TO_DATE(?, '%d/%m/%Y %h:%i:%s') and  " +
                        			"		 prd.start_date  < STR_TO_DATE(?,'%d/%m/%Y %h:%i:%s')  and  mc.cat_desc = ? " +
                        			"        and prd.product_id = pd.product_id and pd.product_cat_id = mc.cat_id " +
                        			"   group by  prd.start_date ";
                        	st = (PreparedStatement) conn.prepareStatement(query);
                        	st.setString(1, start_date);
                        	st.setString(2, end_date);
                        	st.setString(3, catName);
                        }
                        
                        
                        
                        
                        ResultSet res = st.executeQuery();
                          
                        while (res.next()) {
                        	double mrkt_cost = res.getDouble(2)/1000;
                                aSeries.getData().add(new XYChart.Data(res.getString(1),mrkt_cost));
                        }
                        res.close();
                        st.close();
                        conn.close();
                }catch(Exception e){
                        e.printStackTrace();
                }
                return aSeries;
                
        }
    /**
     * This method to return the total sale of a category in a series format.
     * Its used to generate the area chart for total sale in the marketing dash board.
     */
        @SuppressWarnings({ "unchecked", "rawtypes" })
  public XYChart.Series<String,Double> getAreaSaleChart(final String catName, final String quater){
                XYChart.Series< String,Double> aSeries = new XYChart.Series<String,Double>();
                String start_date = null;
                String end_date = null;
                String query = null;
                PreparedStatement st = null;
                try{
                        conn = CreateConnection.getCon();
                        if("Q3 - 2012".equalsIgnoreCase(quater)){
                        	start_date = "01/07/2012";
                        	end_date = "30/9/2012";
                        }else if("Q4 - 2012".equalsIgnoreCase(quater)){
                        	start_date = "01/10/2012";
                        	end_date = "31/12/2012";
                        }
                        
                        
                        if("All".equalsIgnoreCase(catName)){
                        	query = "select    DATE_FORMAT(so.sales_date,GET_FORMAT(DATE,'EUR')), sum(so.net_price) " +
                        			" from sales_order so , product_detail pd, master_category mc " +
                        			" where so.sales_date >= STR_TO_DATE(?, '%d/%m/%Y %h:%i:%s') and " +
                        			"  so.sales_date < STR_TO_DATE(?,'%d/%m/%Y %h:%i:%s')" +
                        			"  and so.product_id = pd.product_id   and pd.product_cat_id = mc.cat_id   group by so.sales_date";
                        	st = (PreparedStatement) conn.prepareStatement(query);
                        	st.setString(1, start_date);
                        	st.setString(2, end_date);
                        }else{
                        	query = "select    DATE_FORMAT(so.sales_date,GET_FORMAT(DATE,'EUR')), sum(so.net_price) " +
                        			" from sales_order so , product_detail pd, master_category mc " +
                        			" where so.sales_date >= STR_TO_DATE(?, '%d/%m/%Y %h:%i:%s') and " +
                        			"  so.sales_date < STR_TO_DATE(?,'%d/%m/%Y %h:%i:%s')" +
                        			"  and so.product_id = pd.product_id   and pd.product_cat_id = mc.cat_id " +
                        			"  and mc.cat_desc = ?   group by so.sales_date";
                        	st = (PreparedStatement) conn.prepareStatement(query);
                        	
                        	st.setString(1, start_date);
                        	st.setString(2, end_date);
                        	st.setString(3, catName);
                        }
                        
                        ResultSet res = st.executeQuery();
                          
                        while (res.next()) {
                        	double sale_cost = res.getDouble(2)/1000;
                                aSeries.getData().add(new XYChart.Data(res.getString(1),sale_cost));
                        }
                        res.close();
                        st.close();
                        conn.close();
                }catch(Exception e){
                        e.printStackTrace();
                }
                return aSeries;
                
        }
        public List<CategoryTo> getAllProductCat2(){
            List<CategoryTo> list = new ArrayList<CategoryTo>();
            CategoryTo to = null;
            try{
                    conn = CreateConnection.getCon();
                    PreparedStatement st = (PreparedStatement) conn.prepareStatement("SELECT * FROM  master_category ");
                            
                    ResultSet res = st.executeQuery();
                    CategoryTo to_all = new CategoryTo();
                    to_all.setId(0);
                    to_all.setDesc("All");
                    list.add(to_all);
                    while (res.next()) {
                            to = new CategoryTo();
                            to.setId(res.getInt("cat_id"));
                            to.setDesc(res.getString("cat_desc"));
                            list.add(to);
                    }
                    res.close();
                    st.close();
                    conn.close();
            }catch(Exception e){
                    e.printStackTrace();
            }
            
            return list;
    }
 @SuppressWarnings({ "rawtypes", "unchecked" })
  public ObservableList getAllCatPie() {
                
                ObservableList data = FXCollections.observableArrayList();
                try {
                        
                        conn = CreateConnection.getCon();
                        
                        String query = "  select mc.cat_desc,sum(so.net_price)from sales_order so,product_detail pd ,master_category mc " +
                                        " where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id   " +
                                        "  group by mc.cat_id";
                        PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
                        
                        ResultSet res = st.executeQuery();
                          
                           DecimalFormat df=new DecimalFormat("0.00");

                        while (res.next()) {
                            double temp = Math.round(res.getDouble(2));
                            
                            String formate = df.format(temp);
                             data.add(new PieChart.Data(res.getString(1),new Double(formate)));

                        }
                        res.close();
                        st.close();
                        conn.close();
                        
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                
                return data;
                
                
        }
 
 public double getCatSalesValue(String catName){
	  double value =0.0;
	   try{
	   conn = CreateConnection.getCon();
	     String query = "  select sum(so.net_price) from sales_order so,product_detail pd ,master_category mc " +
	                 " where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id   " +
	                 " and mc.cat_desc = ? ";
	   PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
	   st.setString(1, catName);
	  ResultSet res = st.executeQuery();
	  DecimalFormat df=new DecimalFormat("0.00");
	  while (res.next()) {
	             double temp = Math.round(res.getDouble(1)/1000);
	             String formate = df.format(temp);
	             value = new Double(formate);

	         }
	         res.close();
	         st.close();
	         conn.close();
	   }catch (SQLException e) {
	         // TODO Auto-generated catch block
	         e.printStackTrace();
	   }
	   return value;
	 }
	 
	 public double getCatMrktValue(String catName){
	  double value =0.0;
	   try{
	  conn = CreateConnection.getCon();
	  String query = "select sum(pd.discount * pd.planed_volume) as Marketing_cost  "
	                 + " from promotion_details pd ,product_detail prd, master_category cat "
	                 + " where pd.product_id = prd.product_id  and prd.product_cat_id = cat.cat_id "
	                 + " and cat.cat_desc = ?  ";
	  PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
	  st.setString(1, catName);
	  ResultSet res = st.executeQuery();
	   DecimalFormat df=new DecimalFormat("0.00");
	   while (res.next()) {
	             double temp = Math.round(res.getDouble(1)/1000);
	             String formate = df.format(temp);
	             value = new Double(formate);

	         }
	         res.close();
	         st.close();
	         conn.close();
	  }catch (SQLException e) {
	         // TODO Auto-generated catch block
	         e.printStackTrace();
	  }
	  return value;
	 }
 
  @SuppressWarnings({ "unchecked", "rawtypes" })
  public XYChart.Series<String,Double> getTargetBarChart(){
                XYChart.Series< String,Double> aSeries = new XYChart.Series<String,Double>();
                try{
                        conn = CreateConnection.getCon();
                        String query = "  select mc.cat_desc,st.target_cost from sales_target st,master_category mc where st.cat_id = mc.cat_id ";
                        PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
                        
                        ResultSet res = st.executeQuery();
                          
                        while (res.next()) {
                                aSeries.getData().add(new XYChart.Data(res.getString(1),res.getDouble(2)));
                                System.out.println(res.getString(1)+"----"+res.getDouble(2));
                        }
                        res.close();
                        st.close();
                        conn.close();
                }catch(SQLException e){
                        e.printStackTrace();
                }
                return aSeries;
                
        }
  @SuppressWarnings({ "unchecked", "rawtypes" })
  public XYChart.Series<String,Double> getMrktBarChart(){
                XYChart.Series< String,Double> aSeries = new XYChart.Series<String,Double>();
                try{
                        conn = CreateConnection.getCon();
                        String query = "select sum(pd.discount * pd.planed_volume) as Marketing_cost , cat.cat_desc "
                        + " from promotion_details pd ,product_detail prd, master_category cat "
                        + " where pd.product_id = prd.product_id  and prd.product_cat_id = cat.cat_id "
                        
                        + " group by  cat.cat_id ; ";
                        PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
                        
                        ResultSet res = st.executeQuery();
                          
                        while (res.next()) {
                                aSeries.getData().add(new XYChart.Data(res.getString(2),res.getDouble(1)));
                               
                        }
                        res.close();
                        st.close();
                        conn.close();
                }catch(SQLException e){
                        e.printStackTrace();
                }
                return aSeries;
                
        }
  
  public double getMrktCost(String cat){
      double mrktVal = 0.0;
      try{
          conn = CreateConnection.getCon();
           String query1 = "select sum(pd.discount * pd.planed_volume) as Marketing_cost "
                        + " from promotion_details pd ,product_detail prd, master_category cat "
                        + " where pd.product_id = prd.product_id  and prd.product_cat_id = cat.cat_id "
                        + " and cat.cat_desc = ?  "
                        + " group by  cat.cat_id  ";
                                        
                PreparedStatement st1 = (PreparedStatement) conn.prepareStatement(query1);
                st1.setString(1, cat);
                
                ResultSet res = st1.executeQuery();
                while (res.next()) {
                        mrktVal  = res.getDouble(1)/1000;
                }
                res.close();
                st1.close();
          
      }catch(Exception e){
          e.printStackTrace();
      }
      return mrktVal;
  }
}
